﻿use master
go
create database Asdf
on
(
	name='Asdft',
	filename='D:\SQL\Asdf.mdf',
	size=5MB,
	maxsize=10MB,
	filegrowth=2MB
)
log on
(
	name='Asdf_log',
	filename='D:\SQL\Asdf_log.ldf',
	size=5MB,
	maxsize=10MB,
	filegrowth=2MB
)
go


use [Asdf]
go
create table ClassInfo
(
	ClassID int primary key identity,
	ClassName nvarchar(20) unique not null,
)

create table Student
(
	StuID int primary key identity(1,1), 
	ClassID int foreign key references ClassInfo(ClassID),
	StuName nvarchar(20) not null,
	StuSex nvarchar(1) check(StuSex='' or StuSex='Ů'),
	StuBirthday date ,
	StuPhone nvarchar(11) unique ,
)

create table Course
(
	CourseID int primary key identity,
	CourseName nvarchar(50) unique not null,
	CourseCredit int default(1) check(CourseCredit>0 and CourseCredit<6) not null
)

create table Score
(
	ScoteID int identity,
	StuID int ,
	CourseID int ,
	Score decimal(5,2) unique not null,
)

alter table Student add StuAddress nvarchar(200) 


alter table Score add constraint PK_Score_ScoreID primary key(ScoteID) 
alter table Score add constraint FK_Score_StuID foreign key(StuID) references Student(StuID)
alter table Score add constraint FK_Score_CourseID foreign key(CourseID) references Course(CourseID)